﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using InterfazConsultas;
using ConsultasEjemplo5deOro.Interfaz;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Data;
using System.Data.Common;

namespace ConsultasEjemplo5deOro.Dominio
{

    public class Consultas5deOro : IConsulta
    {
        private GUIConsultas FormConsulta;

        private string Nombre = "5 de Oro";

        private DbCommand DbComm;

        string[] Numeros = new string[] {"01","02","03","04","05","06","07","08","09","10","11","12",
                                "13","14","15","16","17","18","19","20","21","22","23","24",
                                "25","26","27","28","29","30","31","32","33","34","35","36",
                                "37","38","39","40","41","42","43","44"};

        public Consultas5deOro() 
        {
            this.FormConsulta = new GUIConsultas();
        }

        public Form CargarFormulario()
        {
            return this.FormConsulta;
        }

        public void EjecutarConsulta()
        {
            DbComm = OpenDB();
            DataTable TblDatos = new DataTable("Consultas");
            DataColumn ColumnaNumero;
            ColumnaNumero = new DataColumn();
            ColumnaNumero.ColumnName = "Numero";
            TblDatos.Columns.Add(ColumnaNumero);

            DataColumn ColumnaCantidad;
            ColumnaCantidad = new DataColumn();
            ColumnaCantidad.ColumnName = "Cantidad";
            TblDatos.Columns.Add(ColumnaCantidad);

            string FechaInicial = this.FormConsulta.DevolverFechaInicio().ToShortDateString();
            string FechaFinal = this.FormConsulta.DevolverFechaFin().ToShortDateString();
          
            foreach (string num in Numeros)
            {
                string sentencia = "select count(*) from Apuestas where Juego IN (Select IdJuego from Juegos where Descripcion = 'Juego5deOro') and ValoresApuesta like '%" + num + "%'" + " and Fecha between convert(datetime, " + "'" + FechaInicial + "'" + " , 103) AND convert(datetime, " + "'" + FechaFinal + "'" + " ,103)";

                DbComm.CommandText = sentencia;
                DbComm.CommandType = CommandType.Text;

                DbDataReader reader = DbComm.ExecuteReader();

                while (reader.Read())
                {
                    DataRow Fila = TblDatos.NewRow();

                    Fila["Numero"] = num;
                    Fila["Cantidad"] = reader[0];

                    TblDatos.Rows.Add(Fila);
                }
                reader.Close();
            }
            FormConsulta.CargarDataGrid(TblDatos);
        }

        private DbCommand OpenDB()
        {
            DbConnection connR = new SqlConnection("server=localhost; database = Agencia; Trusted_Connection=true");           
            DbCommand objConn = connR.CreateCommand();
            connR.Open();
            return objConn;
        }

        private void CloseBD(SqlConnection BD)
        {
            BD.Close();
        }

        public string GetNombre() 
        {
            return this.Nombre;
        }
    }

}
